List Builder Wizard

The Pyramid application provides the ability for end users to define and use custom member and sets in the application, as described in the Custom Members/Measures and Custom Sets topic. Both functions allow users to create formulations using logical MDX statements or by MANUALLY adding individual members to an MDX formulation.

Adding members manually is a simple drag and drop process from inside the editor, however it can become cumbersome when needing to add LARGE lists of elements that cannot be better selected using formulae.

The List Builder Wizard is provided to simplify this process by providing a mechanism to either:

• UPLOAD and import a list of items that represent member elements.
• Randomly select members from the target hierarchy/dimension

This list is then translated into an MDX statement as part of a custom member or set and be used throughout the application.

NOTE: The list size cannot exceed 25,000 elements.

Accessing the List Builder

The list builder function is available as a choice in the Custom MDX menu drop down from the Query Ribbon Tab. It is accessible by Professional and Analyst users only. Consumer users can use previously created custom elements.

Using the List Builder

List Imports

The following steps outline the process of importing a list of items.

 

steps
The first panel of the importer allows users to either CUT AND PASTE a listing of items into the window or to import a delimited file via the open file button.   Lists should be in PLAIN TEXT and delimited by a comma, pipe, tilde, semi colon, space or tab character.   Lists can be comprised of multiple columns of data, however only one column can be imported and used at a time.
Once the list is uploaded, users can change the delimiters used on the data. A sample of the first 100 rows is provided for verification processes.   Users need to select the field delimiters, the row/line delimiters and the number of header rows in the listing.   Header rows are ignored in the importation process.   The results of the settings are shown in the grid.   Before continuing users must select which column they wish to import (in the red highlighted section).
The last phase of the import requires users to complete 3 sections:   Users need to select the target of the import. The target indicates which dimension, hierarchy and level the list applies to. Users must also select which aspect of a member the list applies to by selecting a matching property. See detailed explanation below for more.   Next, the user needs to indicate the formulation for the list: will it be used to create a custom member or a custom set. If using a custom member, users must also choose what method of aggregation they are applying to the set.   Last, the user needs to provide some meta data for the new custom element. The name is required and the description is optional. Users must also indicate whether the new custom element will be for their private use only or be available to the other users (public).

Once completed, the new custom element will be found in the target hierarchy's element tree and can be edited using the Custom Member or Custom Sets editor like all other custom elements.

Random List Generation

The random list generator is similar to the above steps for "imported" lists; except the user does not supply an external listing. Instead they choose the number of elements to randomly select (max 5000) and then choose the target dimension/hierarchy (see orange highlight in step 1 image above). Once launched, the BI Office engine will build a random listing and save the new member/set into the content store.

Understanding Cube Structure Targets

Lists create custom elements that reflect selections from the relevant hierarchies in a cube. Before an imported list can be successfully created, the user must identify which hierarchy the list applies to.

As such, the dimension and the relevant hierarchy must be selected. Next, users have to indicate which part of the hierarchy the list applies to. The combination of level and property provide this detail.

The level indicates which members will be used and the property indicates HOW they will be IDENTIFIED in the MDX.

• By default, every level exposes the MEMBER NAME and the MEMBER KEY property (see green highlight below), since every member in a cube has both of these properties.
• Users can also select alternative means of matching the list to members using other member properties if they have been included in the cube design (see red highlight below).

List Builder Example:

A user imports the following list of items. The list has 3 columns one for the product name, one for its "key" and one for its color. The key is derived from the database that stores the list. The user can choose to use each of these 3 fields to identify members in the hierarchy using one of the three relevant properties as shown below.

 

If the user chooses the first column, the appropriate property is the "member name" target property. This will yield an MDX statement as follows

[Product].[Product].[Product].[bike A]

 

If the user chooses the second column, the appropriate property is the "member key" target property. This will yield an MDX statement as follows

[Product].[Product].[Product].&[456]

 

If the user chooses the first column, the appropriate property is the "Color" target property. This will yield an MDX statement as follows

[Product].[Product].[Product].currentmember.properties("color") = "blue"

 

NOTE: The last option is particularly useful when more formulaic selections are required, over and above selecting individual items. However, it will also cause the engine to find ALL member elements that match the formulation.